﻿using System;
using System.Collections.Generic;
using System.Text;

using EdgeStatz.ESSystems.Objects;

namespace EdgeStatz.ModelEngine.FolderTools
{
    public class FolderEngine
    {
        FolderToolsXml xmlFtObj;
        string xmlPath;

        public FolderEngine(string xmlPath)
        {
            SetXMLPath(xmlPath);
            GlobalObjectsSQL.SetConnectionString();
        }

        public void SetXMLPath(string xmlPath)
        {
            this.xmlPath = xmlPath;
            xmlFtObj = new FolderToolsXml(this.xmlPath);
        }

        #region Set Folder Definitions
        
        public void SetFolderDefinitions(FolderDefinition[] value)
        {
            foreach (FolderDefinition fd in value)
                if (fd == null)
                    throw new NullReferenceException("Folder definition cannot be null.");
            xmlFtObj.Folders = value;
        }

        public void SetEnumFolderDefinition(EnumFolderDefinition[] value)
        {
            foreach (EnumFolderDefinition efd in value)
                if (efd == null)
                    throw new NullReferenceException("Enum folder definition cannot be null.");
            xmlFtObj.EnumFolders = value;
        }

        public void AddNewFolder(FolderDefinition value)
        {
            if (value == null)
                throw new NullReferenceException("Folder definition cannot be null.");
            xmlFtObj.AddFolder(value);
        }

        public void AddEnumFolder(EnumFolderDefinition value)
        {
            if (value == null)
                throw new NullReferenceException("Enum folder definition cannot be null.");
            xmlFtObj.AddEnumFolder(value);
        }

        public void RemoveFolderByName(string name)
        {
            xmlFtObj.RemoveFolderByName(name);
        }

        public void RemoveFolderByTableName(string tablename)
        {
            xmlFtObj.RemoveFolderByTableName(tablename);
        }

        public void ReplaceFolderByName(FolderDefinition value, string name)
        {
            if (xmlFtObj.FolderExistsByName(name))
            {
                xmlFtObj.RemoveFolderByName(name);
                xmlFtObj.AddFolder(value);
            }
        }

        public void ReplaceFolderByTableName(FolderDefinition value, string tablename)
        {
            if (xmlFtObj.FolderExistsByTable(tablename))
            {
                xmlFtObj.RemoveFolderByTableName(tablename);
                xmlFtObj.AddFolder(value);
            }
        }

        public void RemoveEnumFolderByName(string name)
        {
            xmlFtObj.RemoveEnumFolderByName(name);
        }

        public void RemoveEnumFolderByTableName(string tablename)
        {
            xmlFtObj.RemoveEnumFolderByTableName(tablename);
        }

        public void ReplaceEnumFolderByName(EnumFolderDefinition value, string name)
        {
            if (xmlFtObj.EnumFolderExistsByName(name))
            {
                xmlFtObj.RemoveEnumFolderByName(name);
                xmlFtObj.AddEnumFolder(value);
            }
        }

        public void ReplaceEnumFolderByTableName(EnumFolderDefinition value, string tablename)
        {
            if (xmlFtObj.EnumFolderExistsByTable(tablename))
            {
                xmlFtObj.RemoveEnumFolderByTableName(tablename);
                xmlFtObj.AddEnumFolder(value);
            }
        }
        #endregion

        #region Set Folder Objects
        public void RemoveFolderObject(string folder, string key)
        {
            object[] a = GetSubFolderObjects(folder, key);
            string current_folder = "";
            foreach (object c in a)
            {
                if (c.GetType().Equals(typeof(string)))
                    current_folder = (string)c;
                else if (c.GetType().Equals(typeof(FolderObj[])))
                {
                    object[] f1 = (object[])c;
                    foreach (object d in f1)
                        RemoveFolderObject(current_folder, ((FolderObj)d).Key);
                }
            }

            FolderDefinition fd = this.GetFolderDefinitionByName(folder);
            if (fd != null)
                GlobalObjectsSQL.ExecuteNonQueryCommand("DELETE FROM " + fd.TableName + " WHERE " + fd.ValueExp + " = '" + key + "'");
        }

        #endregion

        #region Save & Cancel

        public void SaveModelFile()
        {
            xmlFtObj.SaveFile();
        }

        public void CancelChangesToFile()
        {
            xmlFtObj.CancelChanges();
        }

        #endregion

        #region Get Folder Definitions
        
        public FolderDefinition[] GetFolderDefinitions()
        {
            return xmlFtObj.Folders;
        }
        
        public FolderDefinition GetFolderDefinitionByName(string value)
        {
            return xmlFtObj.FolderExistsByName(value) ? xmlFtObj.GetFolderbyName(value) : null;
        }
        
        public FolderDefinition GetFolderDefinitionByTable(string value)
        {
            return xmlFtObj.FolderExistsByTable(value) ? xmlFtObj.GetFolderByTable(value) : null;
        }

        public EnumFolderDefinition GetEnumDefinitionByName(string value)
        {
            return xmlFtObj.EnumFolderExistsByName(value) ? xmlFtObj.GetEnumFolderbyName(value) : null;
        }
        
        public EnumFolderDefinition GetEnumDefinitionByTable(string value)
        {
            return xmlFtObj.EnumFolderExistsByTable(value) ? xmlFtObj.GetEnumFolderByTable(value) : null;
        }
        
        public EnumFolderDefinition[] GetEnumFolderDefinitions()
        {
            return xmlFtObj.EnumFolders;
        }
        
        public string[] GetFolderNames()
        {
            FolderDefinition[] fdef = xmlFtObj.Folders;
            string[] retarray = new string[fdef.Length];

            for(int i=0;i<fdef.Length;i++)
                retarray[i] = fdef[i].Name;
            return retarray;
        }

        #endregion

        #region Get Folder Objects
        
        public object[] GetLevelOneFolders()
        {
            List<object> fl = new List<object> { };
            foreach (FolderDefinition fd in xmlFtObj.Folders)
            {
                List<FolderObj> al = new List<FolderObj> { };
                if (fd.Level.Equals(FolderLevel.One))
                {
                    object[] reader = GetAllRecords(fd);
                    foreach (object obj in reader)
                    {
                        object[] readerObj = (object[])obj;
                        FolderObj fobj = new FolderObj(readerObj[0].ToString(), readerObj[1].ToString(), fd);
                        fobj = AddSubFolderForObj(fobj);
                        al.Add(fobj);
                    }
                    fl.Add(fd.Name);
                    fl.Add(al.ToArray());
                }
            }
            return fl.ToArray();
        }

        public object[] GetLevelTwoFolders()
        {
            List<object> fl = new List<object> { };
            foreach (FolderDefinition fd in xmlFtObj.Folders)
            {
                List<FolderObj> al = new List<FolderObj> { };
                if (fd.Level.Equals(FolderLevel.Two))
                {
                    object[] reader = GetAllRecords(fd);
                    foreach (object obj in reader)
                    {
                        object[] readerObj = (object[])obj;
                        FolderObj fobj = new FolderObj(readerObj[0].ToString(), readerObj[1].ToString(), fd);
                        fobj = AddSubFolderForObj(fobj);
                        al.Add(fobj);
                    }
                    fl.Add(fd.Name);
                    fl.Add(al.ToArray());
                }
            }
            return fl.ToArray();
        }

        private FolderObj AddSubFolderForObj(FolderObj fobj)
        {
            foreach (SubFolderDefinition sfd in fobj.Definition.SubFolders)
            {
                FolderDefinition subFolderRef = xmlFtObj.GetFolderbyName(sfd.Name);
                if (subFolderRef.Level != FolderLevel.Four)
                {
                    object[] reader = GetRecordsFor(fobj.Definition, sfd, fobj.Key);

                    foreach (object obj in reader)
                    {
                        object[] readerObj = (object[])obj;
                        FolderObj folderObj = new FolderObj(readerObj[0].ToString(), readerObj[1].ToString(), subFolderRef);
                        AddSubFolderForObj(folderObj);
                        fobj.AddSubFolderItem(sfd.Name, folderObj);
                    }
                }
            }
            return fobj;
        }

        public object[] GetSubFolderObjects(string foldername, string key)
        {
            FolderDefinition fdef = GetFolderDefinitionByName(foldername); 
            List<object> fl = new List<object> { };
            foreach (SubFolderDefinition sfdef in fdef.SubFolders)
            {
                FolderDefinition sub_fdef = GetFolderDefinitionByName(sfdef.Name);
                if (sub_fdef.Level == FolderLevel.Four)
                    continue;
                if (sfdef.Condition != null && GlobalObjectsSQL.ExecuteReaderQueryCommand("Select * From [" + fdef.TableName + "] Where " + 
                    fdef.ValueExp + " = '" + key + "' AND " + sfdef.Condition).Length == 0)
                    continue;
                List<FolderObj> al = new List<FolderObj> { };
                object[] reader = GetRecordsFor(fdef, sfdef, key);                
                foreach (object obj in reader)
                {
                    object[] readerObj = (object[])obj;
                    FolderObj fobj = new FolderObj(readerObj[0].ToString(), readerObj[1].ToString(), sub_fdef);
                    fobj = AddSubFolderForObj(fobj);
                    al.Add(fobj);
                }
                fl.Add(sub_fdef.Name);
                fl.Add(al.ToArray());
            }
            return fl.ToArray();
        }

        #endregion

        #region Decrypt
        /// <summary>
        /// Returns an array
        /// </summary>
        /// <param name="expression"></param>
        /// <param name="table"></param>
        /// <returns>[0] - columns for select statement
        /// [1] - tables for from statement
        /// [2] - conditions for where statement</returns>
        public static object[] DecryptDisplayCode(string expression, string table)
        {
            if (expression == null || table == null)
                throw new NullReferenceException("Decoder: expression or table cannot be null");
            if (expression.Length > 5 && expression.Substring(0, 5).ToUpper().Equals("EVAL("))
            {
                string[] expArray = expression.Substring(5, expression.Length - 6).Split('\'');
                string display = "";
                string from = "";
                for (int i = 0; i < expArray.Length; i++)
                {
                    if(i > 0 && i < expArray.Length)
                        display += " + ";
                    switch (i % 2)
                    {
                        case 0:
                            display += "\'" + expArray[i] + "\'";
                            break;
                        case 1:
                            if (expArray[i].Contains(".Prompt("))
                            {
                                string tabcol = expArray[i].Substring(0,expArray[i].IndexOf(".Prompt("));
                                string[] tmp = expArray[i].Substring(expArray[i].IndexOf(".Prompt(") + 8,
                                    expArray[i].Length - 1 - 8 - tabcol.Length).Split(',');
                                string reftab = (tmp[0].Split('.'))[0];
                                string refcol = (tmp[0].Split('.'))[1];
                                string rtcol = (tmp[1].Split('.'))[1];
                                display += "(cast([" + reftab + i + "].[" + rtcol + "] as varchar))";
                                
                                from += "Inner join [" + reftab + "] as [" + reftab + i + "] on ([" + table + "].[" + tabcol + "]=[" + reftab + i + "].[" + refcol + "])";
                            }
                            else
                                display += "(cast([" + table + "].[" + expArray[i] + "] as varchar))";
                            break;
                    }
                }
                return new object[] { display, from, "" };
            }
            else
            {
                return new object[] { "[" + table + "].[" + expression + "]", "", "" };
            }
        }
        #endregion 

        #region SQL Queries
        public object[] GetAllRecords(FolderDefinition definition)
        {
            object[] sqldecodeDisp = DecryptDisplayCode(
                        definition.DisplayExp, definition.TableName);
            object[] sqldecodeValue = DecryptDisplayCode(
                        definition.ValueExp, definition.TableName);
            
            string command = "SELECT " + sqldecodeDisp[0] + " as [desc], " +
                        sqldecodeValue[0] + " as [key]" + 
                        " FROM " + definition.TableName + " " + sqldecodeDisp[1] + " " + sqldecodeValue[1] +
                        " ORDER BY " + sqldecodeDisp[0] + " asc";

            return GlobalObjectsSQL.ExecuteReaderQueryCommand(command);
        }

        public object[] GetRecordsFor(FolderDefinition topDefinition, SubFolderDefinition subDefinition, string key)
        {
            FolderDefinition subFolderRef = xmlFtObj.GetFolderbyName(subDefinition.Name);

            object[] sqldecodeDisp = DecryptDisplayCode(
                        subFolderRef.DisplayExp, subFolderRef.TableName);
            object[] sqldecodeValue = DecryptDisplayCode(
                        subFolderRef.ValueExp, subFolderRef.TableName);

            string command = "SELECT " + sqldecodeDisp[0] + " as [desc], " +
                        sqldecodeValue[0] + " as [key]" + 
                        " FROM " + subFolderRef.TableName + 
                        (sqldecodeDisp[1].ToString() == "" ? "" : " " + sqldecodeDisp[1]) +
                        (sqldecodeValue[1].ToString() == "" ? "" : " " + sqldecodeValue[1]) + 
                        " inner join " + topDefinition.TableName +
                        " on (" + subFolderRef.TableName + "." + subDefinition.Column + " = " +
                        topDefinition.TableName + "." + subDefinition.ForeignColumn + " AND " +
                        topDefinition.TableName + "." + subDefinition.ForeignColumn + " = " + "\'" + key + "\')" +
                        " ORDER BY " + (subFolderRef.OrderBy == null ? (sqldecodeDisp[0] + " asc") : subFolderRef.OrderBy);

            return GlobalObjectsSQL.ExecuteReaderQueryCommand(command);
        }
        #endregion
    }
}
